from robot.api import logger
from robotlibcore import keyword

import calendar
import datetime

class ObcModel(object):

    @keyword('Create Oec Bill')
    def create_oec_bill(self, billType, customer_id, year, month):

        template_s = "INSERT INTO `obc_fee_bill` (`customer_id`, `customer_entity_id`, `responsible_branch_id`, `bill_start_time`, `bill_end_time`, `bill_amount`, `bill_status`, `bill_month`, `settle_status`, `send_status`, `settle_amount`, `real_pick_amount`, `modify_amount`, `invoice_amount`, `invoice_status`, `service_type`, `total_detail_num`, `fee_detail_num`, `extra`, `create_time`, `last_modified`) VALUES"
        value_s = "('{0}', '', '001085', '{1}', '{2}', {3}, '{4}', '{5}', '{6}', '{7}', {8}, {9}, {10}, 0, 'noneed', '01', '1', '1', null, '{11}', now())"
        month = int(month)

        bill_start_time = "{}-{}-01 00:00:00".format(year, self._get_month_num(month))
        bill_end_time =  "{} 23:59:59".format(self._get_month_last_day(year, month))
        bill_month = "{}{}".format(year, self._get_month_num(month))
        create_time = "{}-{}-01 00:00:00".format(year, self._get_month_num(month+1))

        if billType == "noneed":
            bill_amount = 0
            bill_status = "confirm"
            settle_status = "noneed"
            send_status = "init"
            settle_amount = 0
            real_pick_amount = 0
            modify_amount = 0
        
        if billType == "unsettle":
            bill_amount = 101.99
            bill_status = "unconfirm"
            settle_status = "unsettle"
            send_status = "init"
            settle_amount = 100.00
            real_pick_amount = 0
            modify_amount = 1.99

        if billType == "settled":
            bill_amount = 101.99
            bill_status = "confirm"
            settle_status = "settled"
            send_status = "send"
            settle_amount = 100.00
            real_pick_amount = 100.00
            modify_amount = 1.99

        if billType == "overdued":
            bill_amount = 101.99
            bill_status = "confirm"
            settle_status = "overdued"
            send_status = "send"
            settle_amount = 100.00
            real_pick_amount = 0.00
            modify_amount = 1.99
        
        if billType == "stlfailed":
            bill_amount = 101.99
            bill_status = "confirm"
            settle_status = "stlfailed"
            send_status = "send"
            settle_amount = 100.00
            real_pick_amount = 0.00
            modify_amount = 1.99

        value_s = value_s.format(customer_id, bill_start_time, bill_end_time, bill_amount, bill_status, bill_month, settle_status, send_status, settle_amount, real_pick_amount, modify_amount, create_time)
        bill = template_s + value_s
        logger.info("[OEC生成账单] sql: {}".format(bill))
        
        return bill
    
    @keyword('Get Bill Settle Day')
    def get_bill_settle_day(self, year, month):

        return "{}-{}-16 00:00:00".format(year, self._get_month_num(int(month)+1))
    
    def _get_month_last_day(self, year, month):

        d = calendar.monthrange(int(year), int(month))

        return "{}-{}-{}".format(year, self._get_month_num(month), d[1])
    
    def _get_month_num(self, month):
        if int(month) < 10:
            return "0{}".format(str(month))
        
        return str(month)
    

    @keyword('Get Year Bill Key')
    def get_year_bill_key(self, customerId, serviceType, year):
        
        return "obc:query:queryByYear:{0}:{1}:{2}".format(customerId, serviceType, year)


    @keyword('Get Obc Unsettle Bill Key')
    def get_obc_unsettle_bill_key(self, customerId, serviceType, limit):

        return "obc:query:getUnsettleBill:{}:{}:{}".format(customerId, serviceType, limit)

    @keyword('Get Fee Rule Key')
    def get_fee_rule_key(self, customerId, serviceType, serviceCode):

        return "obc:query:getFeeRule:{}:{}:{}".format(customerId, serviceType, serviceCode)
    
    @keyword('Check Bill Sql')
    def check_bill_sql(self, result):

        customerId = result['customerId']
        billAmount = result['billAmount']
        # billStatus = result['billStatus']
        billMonth = result['billMonth']
        settleStatus = result['settleStatus']
        settleAmount = result['settleAmount']
        realPickAmount = result['realPickAmount']
        modifyAmount = result['modifyAmount']
        serviceType = result['serviceType']
        totalDetailNum = result['totalDetailNum']
        feeDetailNum = result['feeDetailNum']
        bill_id = result['id']
    
        settleDeadline = result['settleDeadline']

        t_s = "select * from obc_fee_bill where customer_id = '{}' and bill_amount = {} and bill_month = '{}' and settle_status = LOWER('{}') and settle_amount = {} and real_pick_amount = {} and modify_amount = {} and service_type = '{}' and total_detail_num = {} and fee_detail_num = {} and id = {}"

        sql = t_s.format(customerId, billAmount, billMonth, settleStatus, 
            settleAmount, realPickAmount, modifyAmount, self._get_service_type(serviceType),
            totalDetailNum, feeDetailNum, bill_id)

        logger.info("[OEC] 生成的sql: {}".format(sql))
        return sql

    def _get_service_type(self, serviceTypeName):

        if serviceTypeName == "PTS":
            service_type = "01"

        return service_type

    @keyword('Create Fee Rule')
    def create_fee_rule(self, effective, customerId, feeMode, serviceTypeName, fee_id, serviceCode="PTS_JG_SWXD"):

        fee_template_s = "INSERT INTO obc_fee_rule(`id`, `customer_id`, `customer_entity_id`, `service_type`, `service_code`, `fee_mode`, `bought_count`, `fee_unit_mode`, `unit_price`, `extra`, `order_item_detail_id`, `responsible_branch_id`, `start_time`, `end_time`, `enable`, `create_time`, `last_modified`) VALUES "
        fee_value_s = "({0}, '{1}', '', '{2}', '{3}', '{4}', 6000, '{5}', 6000, null, '{6}', '001080', {7}, {8}, 1, now(), now())"
        if int(feeMode) == 1:
            service_type = self._get_service_type(serviceTypeName)
            service_code = serviceCode
            fee_mode = 1
            fee_unint_mode = "01"
            order_item_detail_id = customerId
            start_time, end_time = self._get_start_end_time(effective, 1)

        if int(feeMode) == 2:
            service_type = self._get_service_type(serviceTypeName)
            service_code = serviceCode
            fee_mode = 2
            fee_unint_mode = "01"
            order_item_detail_id = customerId
            start_time, end_time = self._get_start_end_time(effective, 2)

        value_s = fee_value_s.format(fee_id, customerId, service_type, service_code, fee_mode, fee_unint_mode, order_item_detail_id, start_time, end_time)

        fee = fee_template_s + value_s
        logger.info("[OEC生成计费规则] sql: {}".format(fee))
        
        fee_detail = self._create_rule_detail_sql(feeMode)
        return fee, fee_detail

    def _create_rule_detail_sql(self, feeMode):
        detail_template_s = "INSERT INTO obc_fee_rule_detail(`fee_rule_id`, `area_start`, `area_end`, `unit_price`) VALUES"
        detail_value_s = "({0}, {1}, {2}, {3})"
        if int(feeMode) == 1 or 2:
            area_start = 1
            area_end = 2147483647
            unit_price = 6000

        detail = detail_template_s + detail_value_s.format(1, area_start, area_end, unit_price)
        
        return detail
    def _get_start_end_time(self, effective, feeMode):
        if str(effective) == "start":
            start_time = "DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL +1 MONTH), '%Y-%m-%d %h:%m:%s')"
            if feeMode == 2 or 3:
                end_time = "null"
            else:
                end_time = "DATE_FORMAT( date_add(curdate()-day(curdate())+1,interval 1 month), '%Y-%m-%d 00:00:00')"

        if str(effective) == "not-start":
            start_time = "DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL -1 MONTH), '%Y-%m-%d %h:%m:%s')"
            if feeMode == 2 or 3:
                end_time = "null"
            else:
                end_time = "DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL -2 MONTH), '%Y-%m-%d 00:00:00')"
        
        if str(effective) == "end":
            start_time = "DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL +2 MONTH), '%Y-%m-%d %h:%m:%s')"
            end_time = "DATE_FORMAT(date_add(curdate()-day(curdate())+1,interval -1 month), '%Y-%m-%d 00:00:00')"
        
        return start_time, end_time

    @keyword('Check Fee Rule Sql')
    def check_fee_rule_sql(self, result):

        feeId = result['id']
        customerId = result['customerId']
        serviceType = self._get_service_type(result['serviceType'])
        serviceCode = result['serviceCode']
        feeMode = self._convert_fee_mode(result['feeMode'])
        boughtCount = result['boughtCount']
        feeUnitMode = self._convert_fee_unit_mode(result['feeUnitMode'])
        unitPrice = result['unitPrice']
        startTime = result['startTime']
        if self._convert_none_to_string(result['endTime']) is None:
            endTime = 'is null'
        else:
            endTime = "= '{}'".format(result['endTime'])
         
        enable = self._convert_enable(result['enable'])
        
        overUnitPrice = result['overUnitPrice']
        ruleDetails = result['ruleDetails']
        settleDeadline = result['settleDeadline']

        fee_s = "select * from obc_fee_rule where id = {} and customer_id = '{}' and service_type = '{}' and service_code = '{}' and fee_mode = '{}' and bought_count = {} and fee_unit_mode = '{}' and unit_price = {} and start_time = '{}' and end_time {} and enable = {}"
        value = fee_s.format(feeId, customerId, serviceType, serviceCode, feeMode, boughtCount,
            feeUnitMode, unitPrice, startTime, endTime, enable)

        logger.info("[OEC] 生成检查的sql: {}".format(value))
        return value
    
    def _convert_none_to_string(self, p):
        if str(p).lower() == "none":
            return None

    def _convert_fee_mode(self, feeModeName):

        if feeModeName == "PREPAID":
            return "1"

        if feeModeName == "POSTPAID":
            return "2"

    def _convert_enable(self, enableName):
        if str(enableName).lower() == "true":
            return 1
        else:
            return 0
    
    def _convert_fee_unit_mode(self, unitModeName):
        if str(unitModeName) == "FIXED":
            return "01"
        
        if str(unitModeName) == "STEP":
            return "02"
        
        if str(unitModeName) == "OTHER":
            return "03"